package com.example.score.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface ScoreMapper {
    
    /**
     * 获取学生分数
     */
    @Select("SELECT score FROM t_student_submit WHERE exam_detail_id = #{examId} AND student_id = #{studentId}")
    Double getStudentScore(@Param("examId") Long examId, @Param("studentId") String studentId);
    
    /**
     * 获取学生班级排名
     */
    @Select("SELECT t1.`rank` FROM " +
            "(SELECT student_id, " +
            "@rank := IF(@prev = score, @rank, @row) AS `rank`, " +
            "@row := @row + 1, " +
            "@prev := score " +
            "FROM t_student_submit, " +
            "(SELECT @rank := 1, @row := 1, @prev := NULL) r " +
            "WHERE exam_detail_id = #{examId} AND class_id = #{classId} " +
            "ORDER BY score DESC) t1 " +
            "WHERE student_id = #{studentId}")
    Integer getStudentClassRank(@Param("examId") Long examId, @Param("classId") Long classId, 
            @Param("studentId") String studentId);
    
    /**
     * 获取班级平均分
     */
    @Select("SELECT AVG(score) FROM t_student_submit WHERE exam_detail_id = #{examId} AND class_id = #{classId} and submit_status = 1")
    Double getClassAverage(@Param("examId") Long examId, @Param("classId") Long classId);
    
    /**
     * 获取全体平均分
     */
    @Select("SELECT AVG(score) FROM t_student_submit WHERE exam_detail_id = #{examId} and submit_status = 1")
    Double getTotalAverage(@Param("examId") Long examId);

    /**
     * 获取学生小题分详情
     */
    @Select("SELECT " +
            "qi.question_num as questionNum, " +
            "qi.question_alias as questionAlias," +
            "qi.question_type_name as questionTypeName, " +
            "qi.question_score as questionScore, " +
            "qi.question_alias as questionAlias," +
            "qs.score as score, " +
            "ss.paper_images as paperImages " +
            "FROM t_question_info qi " +
            "LEFT JOIN t_question_score qs ON qi.question_id = qs.question_id " +
            "LEFT JOIN t_student_submit ss ON qs.student_submit_id = ss.id " +
            "WHERE ss.exam_detail_id = #{examId} " +
            "AND ss.student_id = #{studentId} " +
            "ORDER BY qi.question_num")
    List<Map<String, Object>> getQuestionScores(@Param("examId") Long examId, @Param("studentId") String studentId);
} 